﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CMISLibrary.Entities;
using CMISLibrary;
using System.Globalization;
using BCNPCObject.DB;


namespace BCNPCObject
{
    public class CN_THEODOINO_Control
    {
        #region   Atributes

    
        private DataTable dt = new DataTable();
        private CN_THEODOINO_Info info;
        private DataSet data;

        #endregion

        #region   Constructor

        public CN_THEODOINO_Control()
        {
            info = new CN_THEODOINO_Info();
           
        }
        public CN_THEODOINO_Control(CN_THEODOINO_Info Info)
        {
            this.info = Info;
        }
        
        #endregion

        #region   Properties

        public CN_THEODOINO_Info pInfor
        {
            get { return info; }
            set { info = value; }
        }
      
       
        #endregion

        public DataSet CN_So_BBGiao_Nutgoc(string ma_tngan, string ma_dvqly)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON ).ToArray();
            if (id_hdon_cn_offline.Count() > 0)
            {
                if (so_bbgiao.Count() > 0)
                {
                    id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                                 where so_bbgiao.Contains(b.SO_BBGIAO)
                                 select b.ID_HDON).ToArray();
                    if (id_hoadon.Count() > 0)
                    {
                        var id = (from b in CMIS2.DB.CN_HDON_GTHU
                                  where so_bbgiao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == ma_dvqly
                                  select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                        var ketqua = (from e in CMIS2.DB.CN_THEODOINO
                                      where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                      select new
                                      {
                                          e.KY,
                                          e.MA_SOGCS,
                                          TTIEN_NOP = e.TIEN_PSINH + e.THUE_PSINH,
                                          TIEN_NOP = e.TIEN_PSINH,
                                          THUE_NOP = e.THUE_PSINH,
                                          e.ID_HDON,
                                          e.THANG,
                                          e.NAM,
                                          e.MA_KHANG,
                                          e.LOAI_HDON,
                                          e.TEN_KHANG,
                                          e.DCHI_KHANG,
                                      }).ToList();
                        var kq = (from c in ketqua
                                  join d in id on c.ID_HDON equals d.ID_HDON
                                  select new
                                  {
                                      d.SO_BBGIAO,
                                      d.MA_SOGCS,
                                      TONG_TIEN = c.TTIEN_NOP,
                                      TIEN_NO = c.TIEN_NOP,
                                      THUE_NO = c.THUE_NOP,
                                      c.ID_HDON,
                                      c.THANG,
                                      c.NAM,
                                      c.MA_KHANG,
                                      c.LOAI_HDON,
                                      c.TEN_KHANG,
                                      c.DCHI_KHANG,
                                      c.KY
                                  }).ToList();
                        DataTable dt = Utility.LINQToDataTable(kq);
                        data.Tables.Add(dt);
                        return data;
                    }
                    return null;
                } 
                return null;
            }
            return null;
        }

        public DataSet CN_So_BBGiao_Nutcha(string ma_tngan, int so_BBGIAO, string ma_dvqly)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao.Count() == 0) return null;

            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where so_bbgiao.Contains(b.SO_BBGIAO)
                         select b.ID_HDON).ToArray();
            if (id_hoadon.Count() == 0) return null;

            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON ).ToArray();
            if (id_hdon_cn_offline.Count() > 0)
            {
                var id = (from b in CMIS2.DB.CN_HDON_GTHU
                          where so_bbgiao.Contains(b.SO_BBGIAO)
                          select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                var ketqua = (from e in CMIS2.DB.CN_THEODOINO
                              where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                              select new
                              {
                                  e.KY,
                                  e.MA_SOGCS,
                                  TTIEN_NOP = e.TIEN_NO + e.THUE_NO,
                                  TIEN_NOP = e.TIEN_NO,
                                  THUE_NOP = e.THUE_NO,
                                  e.ID_HDON,
                                  e.THANG,
                                  e.NAM,
                                  e.MA_KHANG,
                                  e.LOAI_HDON,
                                  e.TEN_KHANG,
                                  e.DCHI_KHANG
                              }).ToList();
                var kq = (from c in ketqua
                          join d in id on c.ID_HDON equals d.ID_HDON
                          where d.SO_BBGIAO == so_BBGIAO
                          select new
                          {
                              d.SO_BBGIAO,
                              d.MA_SOGCS,
                              TONG_TIEN = c.TTIEN_NOP,
                              TIEN_NO = c.TIEN_NOP,
                              THUE_NO = c.THUE_NOP,
                              c.ID_HDON,
                              c.THANG,
                              c.NAM,
                              c.MA_KHANG,
                              c.LOAI_HDON,
                              c.TEN_KHANG,
                              c.DCHI_KHANG,
                              c.KY
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                data.Tables.Add(dt);
                return data;
            }
            return null;
        }

        public DataSet CN_So_BBGiao_Nutcon(string ma_tngan, int so_BBGIAO, string Ma_SoGCS, string ma_dvqly)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan
                         select a.SO_BBGIAO).ToArray();
            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where so_bbgiao.Contains(b.SO_BBGIAO) && b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                         select b.ID_HDON).ToArray();
            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON ).ToArray();
            if (id_hdon_cn_offline.Count() > 0)
            {
                var id = (from b in CMIS2.DB.CN_HDON_GTHU
                          where so_bbgiao.Contains(b.SO_BBGIAO) && b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                          select new { b.ID_HDON, so_BBGIAO, Ma_SoGCS }).ToList();

                var theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                 where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                 select e).ToList();
                var kq = (from c in theodoino
                          join d in id on c.ID_HDON equals d.ID_HDON
                          select new
                          {
                              d.so_BBGIAO,
                              d.Ma_SoGCS,
                              c.TIEN_NO,
                              c.THUE_NO,
                              c.ID_HDON,
                              c.THANG,
                              c.NAM,
                              c.MA_KHANG,
                              c.LOAI_HDON,
                              c.TEN_KHANG,
                              c.DCHI_KHANG,
                              c.KY,
                              TONG_TIEN = c.TIEN_NO + c.THUE_NO
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                data.Tables.Add(dt);
                return data;
            }
            return null;
        }

        public DataSet CN_Select(string ma_tngan, string ma_dvqly)
        {
            DataSet ds = new DataSet();
            Int64[] BBG;
            Int64[] hdon;
            Int64[] id_hdon;
            BBG = (from a in CMIS2.DB.CN_BB_GTHU
                   where a.MA_TNGAN == ma_tngan
                   select a.SO_BBGIAO).ToArray();
            if (BBG.Count() > 0)
            {
                hdon = (from a in CMIS2.DB.CN_HDON_GTHU
                        where BBG.Contains(a.SO_BBGIAO)
                        select a.ID_HDON).ToArray();
                if (hdon.Count() > 0)
                {
                    id_hdon = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                               where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                               select a.ID_HDON).ToArray();
                    var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                     where a.TIEN_NO > 0 && a.TTRANG_SSAI != 2 && hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && !id_hdon.Contains(a.ID_HDON)
                                     select a).ToList();
                    var hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                     where BBG.Contains(a.SO_BBGIAO)
                                     select new { a.ID_HDON, a.SO_BBGIAO, a.MA_SOGCS }).ToList();
                    var kq = (from a in theodoino
                              join b in hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.MA_KHANG,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  a.TIEN_NO,
                                  a.THUE_NO,
                                  TONG_TIEN = a.TIEN_NO + a.THUE_NO,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  b.SO_BBGIAO,
                                  b.MA_SOGCS
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    ds.Tables.Add(dt);

                    return ds;
                } return null;
            } return null;
        }

        public DataSet ATM_Ton_Select(string ma_tngan, string ma_dvqly)
        {
            Int64[] id_hdon;
            Int64[] so_bb_gthu;
            string[] ma_gd;

            data = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            var gdich_atm = (from a in CMIS2.DB.NPC_GDICH_ATM
                             where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                             select new { a.NGAY_TTOAN }).ToList();

            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    so_bb_gthu = (from a in CMIS2.DB.CN_BB_GTHU
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.SO_BBGIAO).ToArray();
                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly && so_bb_gthu.Contains(a.SO_BBGIAO)
                                        select a).ToList();

                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG,
                                      b.KY,
                                      b.THANG,
                                      b.NAM
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  NGAY_CHAMNO = a.NGAY_NOP,
                                  a.TIEN_GTGT,
                                  a.SO_TIEN,
                                  a.TONG_TIEN,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  b.MA_SOGCS,
                                  a.MA_KHANG,
                                  b.SO_BBGIAO
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;             
        }

        public string Ten_Tngan(string ma_tngan, string ma_dviqly)
        {
            string ten_tngan = "";
            var t_ngan = (from a in CMIS2.DB.D_TNGAN_VIEN
                         where a.MA_DVIQLY == ma_dviqly && a.MA_TNGAN == ma_tngan.ToUpper()
                         select new { a.TEN_TNGAN }).ToList();
            DataTable dt = Utility.LINQToDataTable(t_ngan);
            if (dt != null && dt.Rows.Count > 0)
            {
                ten_tngan = dt.Rows[0]["TEN_TNGAN"].ToString();
                return ten_tngan;
            }
            return null;
        }

        public DataSet Load_TNV(string ma_dviqly)
        {
            Int64[] id_chamno_off;
            Int64[] id_hdon;
            Int64[] so_bbgiao;
            string[] thungan;
            id_chamno_off = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.MA_DVIQLY == ma_dviqly
                             select a.ID_HDON).ToArray();
            if (id_chamno_off.Count() == 0) return null;

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dviqly
                         select a.SO_BBGIAO).ToArray();

            id_hdon = (from a in CMIS2.DB.CN_HDON_GTHU
                       where a.MA_DVIQLY == ma_dviqly && so_bbgiao.Contains(a.SO_BBGIAO)
                       select a.ID_HDON).ToArray();

            if (id_hdon == null) return null;

            var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                where !id_chamno_off.Contains(a.ID_HDON) && a.TIEN_NO > 0 && a.TTRANG_SSAI != 2 && a.MA_DVIQLY == ma_dviqly
                                select new
                                {
                                    TIEN_NOP = a.TIEN_NO,
                                    THUE_NOP = a.THUE_NO,
                                    TTIEN = a.TIEN_NO + a.THUE_NO,
                                    a.ID_HDON,
                                    a.MA_KHANG,
                                    a.TEN_KHANG,
                                    a.DCHI_KHANG,
                                    a.KY,
                                    a.THANG,
                                    a.NAM,
                                    a.LOAI_HDON
                                }).ToList();

            var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                where so_bbgiao.Contains(a.SO_BBGIAO) && a.MA_DVIQLY == ma_dviqly
                                select new { a.SO_BBGIAO, a.MA_SOGCS, a.ID_HDON }).ToList();

            var t_ngan = (from a in CMIS2.DB.CN_BB_GTHU
                          join b in CMIS2.DB.D_TNGAN_VIEN on a.MA_TNGAN equals b.MA_TNGAN
                          where a.MA_DVIQLY == ma_dviqly
                          select new { a.SO_BBGIAO, b.TEN_TNGAN, b.MA_TNGAN }).ToList();

            var hdon_tngan = (from a in cn_hdon_gthu
                              join b in t_ngan on a.SO_BBGIAO equals b.SO_BBGIAO
                              select new { a.SO_BBGIAO, a.MA_SOGCS, a.ID_HDON, b.TEN_TNGAN, b.MA_TNGAN }).ToList();

            var kq = (from a in cn_theodoino
                      join b in hdon_tngan on a.ID_HDON equals b.ID_HDON
                      select new
                      {
                          a.ID_HDON,
                          a.MA_KHANG,
                          a.TEN_KHANG,
                          a.DCHI_KHANG,
                          a.KY,
                          a.NAM,
                          a.THANG,
                          a.LOAI_HDON,
                          b.MA_SOGCS,
                          b.SO_BBGIAO,
                          TIEN_NO = a.TIEN_NOP,
                          THUE_NO = a.THUE_NOP,
                          TONG_TIEN = a.TTIEN,
                          b.MA_TNGAN,
                          b.TEN_TNGAN
                      }).ToList();

            thungan = kq.Select(c => c.MA_TNGAN ).Distinct().ToArray();
            var t_ngan2 = (from a in CMIS2.DB.D_TNGAN_VIEN
                           where a.MA_DVIQLY == ma_dviqly && thungan.Contains(a.MA_TNGAN)
                           select new {a.TEN_TNGAN, a.MA_TNGAN }).ToList();
            
            DataTable dt = Utility.LINQToDataTable(t_ngan2);
            if (dt == null) return null;
            DataSet data = new DataSet();
            data.Tables.Add(dt);
            return data;
        }

        public DataSet Catdien_Nutgoc(string ma_dvqly, DateTime ngay)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao == null) return null;

            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly
                                  select a.ID_HDON).ToArray();
            if (id_hdon_cn_offline.Count() > 0)
            {
                id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                             where so_bbgiao.Contains(b.SO_BBGIAO)
                             select b.ID_HDON).ToArray();
                if (id_hoadon.Count() > 0)
                {
                    var cn_hdon_gthu = (from b in CMIS2.DB.CN_HDON_GTHU
                                        where b.MA_DVIQLY == ma_dvqly
                                        select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                    var bb_hdon = (from a in CMIS2.DB.CN_BB_GTHU
                                   join b in CMIS2.DB.CN_HDON_GTHU
                                       on a.SO_BBGIAO equals b.SO_BBGIAO
                                   where a.MA_DVIQLY == ma_dvqly && b.MA_DVIQLY == ma_dvqly
                                   select new { a.SO_BBGIAO, b.ID_HDON, a.NGAY_GIAO, b.MA_SOGCS }).ToList();

                    var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                        where e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                        select e).ToList();
                    var kq = from a in cn_theodoino
                             join b in bb_hdon on a.ID_HDON equals b.ID_HDON
                             where b.NGAY_GIAO < ngay
                             select new
                             {
                                 a.ID_HDON,
                                 a.TEN_KHANG,
                                 a.DCHI_KHANG,
                                 a.MA_KHANG,
                                 a.KY,
                                 a.THANG,
                                 a.NAM,
                                 b.SO_BBGIAO,
                                 b.MA_SOGCS,
                                 b.NGAY_GIAO,
                                 a.TIEN_NO,
                                 a.THUE_NO,
                                 TONG_TIEN = a.TIEN_NO + a.THUE_NO
                             };
                    DataTable dt = Utility.LINQToDataTable(kq);
                    if (dt == null) return null;
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;
        }

        public DataSet Catdien_Nutcha(string ma_tngan, string ma_dvqly, DateTime ngay)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;

            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao == null) return null;

            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON).ToArray();
            if (id_hdon_cn_offline.Count() > 0)
            {

                id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                             where so_bbgiao.Contains(b.SO_BBGIAO)
                             select b.ID_HDON).ToArray();
                if (id_hoadon.Count() > 0)
                {
                    var cn_hdon_gthu = from b in CMIS2.DB.CN_HDON_GTHU
                                       where b.MA_DVIQLY == ma_dvqly
                                       select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS };

                    var bb_hdon = (from a in CMIS2.DB.CN_BB_GTHU
                                   join b in CMIS2.DB.CN_HDON_GTHU
                                       on a.SO_BBGIAO equals b.SO_BBGIAO
                                   where a.MA_DVIQLY == ma_dvqly && b.MA_DVIQLY == ma_dvqly
                                   select new { a.SO_BBGIAO, b.ID_HDON, a.NGAY_GIAO, b.MA_SOGCS }).ToList();

                    var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                        where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                        select new
                                        {

                                            e.MA_SOGCS,
                                            TONG_TIEN = e.TIEN_PSINH + e.THUE_PSINH,
                                            TIEN_NO = e.TIEN_PSINH,
                                            THUE_NO = e.THUE_PSINH,
                                            e.ID_HDON,
                                            e.KY,
                                            e.THANG,
                                            e.NAM,
                                            e.MA_KHANG,
                                            e.LOAI_HDON,
                                            e.TEN_KHANG,
                                            e.DCHI_KHANG,
                                        }).ToList();
                    var kq = (from a in cn_theodoino
                              join b in bb_hdon on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  a.MA_KHANG,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  b.SO_BBGIAO,
                                  b.MA_SOGCS,
                                  a.TIEN_NO,
                                  a.THUE_NO,
                                  TONG_TIEN = a.TONG_TIEN,
                                  b.NGAY_GIAO
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    if (dt == null) return null;
                    data.Tables.Add(dt);
                    return data;
                }
                return null;
            }
            return null;

        }

        public DataSet Catdien_Nutcha2(string ma_tngan, Int64 so_BBGIAO, string ma_dvqly, DateTime ngay)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();

            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where so_bbgiao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == ma_dvqly
                         select b.ID_HDON).ToArray();

            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON).ToArray();

            if (id_hdon_cn_offline.Count() > 0)
            {
                var cn_hdon_gthu = (from b in CMIS2.DB.CN_HDON_GTHU
                                    where so_bbgiao.Contains(b.SO_BBGIAO)
                                    select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                var bb_hdon = (from a in CMIS2.DB.CN_BB_GTHU
                               join b in CMIS2.DB.CN_HDON_GTHU
                                   on a.SO_BBGIAO equals b.SO_BBGIAO
                               where a.MA_DVIQLY == ma_dvqly && b.MA_DVIQLY == ma_dvqly
                               select new { a.SO_BBGIAO, b.ID_HDON, a.NGAY_GIAO, b.MA_SOGCS }).ToList();

                var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                    where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                    select new
                                    {
                                        e.KY,
                                        e.MA_SOGCS,
                                        TONG_TIEN = e.TIEN_NO + e.THUE_NO,
                                        e.TIEN_NO,
                                        e.THUE_NO,
                                        e.ID_HDON,
                                        e.THANG,
                                        e.NAM,
                                        e.MA_KHANG,
                                        e.LOAI_HDON,
                                        e.TEN_KHANG,
                                        e.DCHI_KHANG
                                    }).ToList();
                var kq = (from c in cn_theodoino
                          join d in bb_hdon on c.ID_HDON equals d.ID_HDON
                          where d.SO_BBGIAO == so_BBGIAO
                          select new
                          {
                              d.SO_BBGIAO,
                              d.MA_SOGCS,
                              c.TONG_TIEN,
                              c.TIEN_NO,
                              c.THUE_NO,
                              c.ID_HDON,
                              c.THANG,
                              c.NAM,
                              c.MA_KHANG,
                              c.LOAI_HDON,
                              c.TEN_KHANG,
                              c.DCHI_KHANG,
                              c.KY,
                              d.NGAY_GIAO
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                if (dt == null) return null;
                data.Tables.Add(dt);
                return data;
            }
            return null;
        }

        public DataSet Catdien_Nutcon(string ma_tngan, Int64 so_BBGIAO, string Ma_SoGCS, string ma_dvqly, DateTime ngay)
        {
            data = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            Int64[] id_hdon_cn_offline;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();

            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                         select b.ID_HDON).ToArray();

            id_hdon_cn_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.MA_DVIQLY == ma_dvqly && a.MA_TNGAN == ma_tngan
                                  select a.ID_HDON).ToArray();

            if (id_hdon_cn_offline.Count() > 0)
            {
                var cn_hdon_gthu = (from b in CMIS2.DB.CN_HDON_GTHU
                                    where b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                                    select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                var bb_hdon = (from a in CMIS2.DB.CN_BB_GTHU
                               join b in CMIS2.DB.CN_HDON_GTHU
                                   on a.SO_BBGIAO equals b.SO_BBGIAO
                               where a.MA_DVIQLY == ma_dvqly && b.MA_DVIQLY == ma_dvqly
                               select new { a.SO_BBGIAO, b.ID_HDON, a.NGAY_GIAO, b.MA_SOGCS }).ToList();

                var cn_theodoino = (from e in CMIS2.DB.CN_THEODOINO
                                    where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && !id_hdon_cn_offline.Contains(e.ID_HDON)
                                    select e).ToList();
                var kq = (from c in cn_theodoino
                          join d in bb_hdon on c.ID_HDON equals d.ID_HDON
                          select new
                          {
                              d.SO_BBGIAO,
                              d.MA_SOGCS,
                              c.TIEN_NO,
                              c.THUE_NO,
                              c.ID_HDON,
                              c.THANG,
                              c.NAM,
                              c.MA_KHANG,
                              c.LOAI_HDON,
                              c.TEN_KHANG,
                              c.DCHI_KHANG,
                              c.KY,
                              TONG_TIEN = c.TIEN_NO + c.THUE_NO,
                              d.NGAY_GIAO
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                data.Tables.Add(dt);
                if (dt == null) return null;
                return data;
            }
            return null;
        }
    }
}
